﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Entities;
using DAL.Exceptions;

namespace DAL
{
    public class DataService
    {
        private ShiftCreatorDBEntities m_DBApplContext = null;
        private static volatile DataService s_Instance = null;
        private static object s_InstanceLockObj = new Object();
        private static object s_DbAccessLockObject = new Object();

        private DataService() 
        {
            m_DBApplContext = new ShiftCreatorDBEntities();
        }

        public static DataService Instance
        {
            get
            {
                if (s_Instance == null)
                {
                    lock (s_InstanceLockObj)
                    {
                        if (s_Instance == null)
                        {
                            s_Instance = new DataService();
                        }
                    }
                }

                return s_Instance;
            }
        }

        #region Manager Table CRUD
        public IEnumerable<Manager> GetAllManagers()
        {
            return m_DBApplContext.Managers;
        }

        public Manager GetManagerByID(string id)
        {
            Manager managerToReturn = getManagerByID(id);
            if (managerToReturn == null)
            {
                throw new DALException(CommonMessagesGenerator.GenerateNotExistObjectMessage("Manager", id, null));
            }
            return managerToReturn;
        }

        private Manager getManagerByID(string id)
        {
            Manager managerToReturn = null;
            managerToReturn = m_DBApplContext.Managers.SingleOrDefault(p => p.ManagerID == id);
            return managerToReturn;
        }

        public Manager GetManagerByUserNameAndPassword(string userName, string password)
        {
            Manager workerToReturn = null;
            workerToReturn = m_DBApplContext.Managers.SingleOrDefault(p => p.UserName == userName && p.Password == password);
            return workerToReturn;
        }

        public void DeleteManager(string managerID, bool saveChanges = false)
        {
            lock (s_DbAccessLockObject)
            {
                Manager toDelete = getManagerByID(managerID);
                if (toDelete != null)
                {
                    m_DBApplContext.Managers.DeleteObject(toDelete);
                    if (saveChanges)
                    {
                        try
                        {
                            m_DBApplContext.SaveChanges();
                        }
                        catch (Exception ex)
                        {
                            throw new DALException(CommonMessagesGenerator.GenerateDBException(ex.Message, "DeleteManager"));  
                        }
                    }
                }
                else
                {
                    throw new DALException(CommonMessagesGenerator.GenerateAllreadyExistObjectMessage("Manager", managerID, null));
                }
            }
        }

        public void AddManager(Manager managerToAdd, bool saveChanges = false)
        {
            lock (s_DbAccessLockObject)
            {
                Manager manager = getManagerByID(managerToAdd.ManagerID);
                if (manager == null)
                {
                    m_DBApplContext.Managers.AddObject(managerToAdd);
                    if (saveChanges)
                    {
                        try
                        {
                            m_DBApplContext.SaveChanges();
                        }
                        catch (Exception ex)
                        {
                            throw new DALException(CommonMessagesGenerator.GenerateDBException(ex.Message, "AddManager")); 
                        }
                    }
                }
                else
                {
                    throw new DALException(CommonMessagesGenerator.GenerateNotExistObjectMessage("Manager", manager.ManagerID, null));
                }
            }
        }
        
        public void UpdateManagerName(string id, string newName, bool saveChanges = false)
        {
            lock (s_DbAccessLockObject)
            {
                Manager managerToUpdate = getManagerByID(id);
                if (managerToUpdate != null)
                {
                    managerToUpdate.FirstName = newName;
                    if (saveChanges)
                    {
                        try
                        {
                            m_DBApplContext.SaveChanges();
                        }
                        catch (Exception ex)
                        {
                            throw new DALException(CommonMessagesGenerator.GenerateDBException(ex.Message, "UpdateManagerName"));
                        }
                    }
                }
                else
                {
                    throw new DALException(CommonMessagesGenerator.GenerateNotExistObjectMessage("Manager", id, null));

                }
            }
        }

        public void UpdateManagerLastName(string id, string newLastName, bool saveChanges = false)
        {
            lock (s_DbAccessLockObject)
            {
                Manager managerToUpdate = getManagerByID(id);
                if (managerToUpdate != null)
                {
                    managerToUpdate.LastName = newLastName;
                    try
                    {
                        m_DBApplContext.SaveChanges();
                    }
                    catch (Exception ex)
                    {
                        throw new DALException(CommonMessagesGenerator.GenerateDBException(ex.Message, "UpdateManagerLastName"));
                    }
                }
                else
                {
                    throw new DALException(CommonMessagesGenerator.GenerateNotExistObjectMessage("Manager", id, null));
                }
            }
        }

        public void UpdateManagerEmailAdress(string id, string emailAdress, bool saveChanges = false)
        {
            lock (s_DbAccessLockObject)
            {
                Manager managerToUpdate = getManagerByID(id);
                if (managerToUpdate != null)
                {
                    managerToUpdate.EmailAdress = emailAdress;
                    try
                    {
                        m_DBApplContext.SaveChanges();
                    }
                    catch (Exception ex)
                    {
                        throw new DALException(CommonMessagesGenerator.GenerateDBException(ex.Message, "UpdateManagerEmailAdress"));
                    }
                }
                else
                {
                    throw new DALException(CommonMessagesGenerator.GenerateNotExistObjectMessage("Manager", id, null));
                }
            }
        }

        public void UpdateManagerToUpdateBirthDate(string id, DateTime newBirthDate, bool saveChanges = false)
        {
            lock (s_DbAccessLockObject)
            {
                Manager managerToUpdate = getManagerByID(id);
                if (managerToUpdate != null)
                {
                    managerToUpdate.BirthDate = newBirthDate;
                    if (saveChanges)
                    {
                        try
                        {
                            m_DBApplContext.SaveChanges();
                        }
                        catch (Exception ex)
                        {
                            throw new DALException(CommonMessagesGenerator.GenerateDBException(ex.Message, "UpdateManagerToUpdateBirthDate"));
                        }
                    }
                }
                else
                {
                    throw new DALException(CommonMessagesGenerator.GenerateNotExistObjectMessage("Manager", id, null));                  
                }
            }
        }

        public void UpdateManagerUserName(string id, string newUserName, bool saveChanges = false)
        {
            lock (s_DbAccessLockObject)
            {
                Manager managerToUpdate = getManagerByID(id);
                if (managerToUpdate != null)
                {
                    managerToUpdate.UserName = newUserName;
                    if (saveChanges)
                    {
                        try
                        {
                            m_DBApplContext.SaveChanges();
                        }
                        catch (Exception ex)
                        {
                            throw new DALException(CommonMessagesGenerator.GenerateDBException(ex.Message, "UpdateManagerUserName"));
                        }
                    }
                }
                else
                {
                    throw new DALException(CommonMessagesGenerator.GenerateNotExistObjectMessage("Manager", id, null));                  
                }
            }
        }

        public void UpdateManagerPassword(string id, string newPassword, bool saveChanges = false)
        {
            lock (s_DbAccessLockObject)
            {
                Manager managerToUpdate = getManagerByID(id);
                if (managerToUpdate != null)
                {
                    managerToUpdate.Password = newPassword;
                    if (saveChanges)
                    {
                        try
                        {
                            m_DBApplContext.SaveChanges();
                        }
                        catch (Exception ex)
                        {
                            throw new DALException(CommonMessagesGenerator.GenerateDBException(ex.Message, "UpdateManagerPassword"));
                        }
                    }
                }
                else
                {
                    throw new DALException(CommonMessagesGenerator.GenerateNotExistObjectMessage("Manager", id, null));                  
                }
            }
        }

        #endregion
        # region Worker Table CRUD
        public IEnumerable<Worker> GetAllWorkers()
        {
            return m_DBApplContext.Workers;   
        }

        public List<string> GetAllWorkersIds(string shiftID, string type)
        {
            List<string> workers = new List<string>();
            lock (s_DbAccessLockObject)
            {
                Console.WriteLine("GetAllWorkersIds");
                IQueryable<string> query = from worker in m_DBApplContext.Workers
                                            select worker.WorkerID;
                foreach (string workerId in query)
                {
                    workers.Add(workerId);
                }
            }
            return workers;
        }

        public Worker GetWorkerByID(string id)
        {
            Worker workerToReturn = null;
            lock (s_DbAccessLockObject)
            {
                workerToReturn = getWorkerByID(id);
                if (workerToReturn == null)
                {
                    throw new DALException(CommonMessagesGenerator.GenerateNotExistObjectMessage("Worker", id, null));
                }
            }
            return workerToReturn;
        }

        private Worker getWorkerByID(string id)
        {
            Worker workerToReturn = null;
            lock (s_DbAccessLockObject)
            {
                workerToReturn = m_DBApplContext.Workers.SingleOrDefault(p => p.WorkerID == id);    
            }
            return workerToReturn;
        }

        public Worker GetWorkerByUserNameAndPassword(string userName, string password)
        {
            Worker workerToReturn = null;
            workerToReturn = m_DBApplContext.Workers.SingleOrDefault(p => p.UserName == userName && p.Password == password);
            return workerToReturn;
        }

        public void DeleteWorker(string workerID, bool saveChanges = false)
        {
            lock (s_DbAccessLockObject)
            {
                Worker toDelete = getWorkerByID(workerID);
                if (toDelete != null)
                {
                    m_DBApplContext.Workers.DeleteObject(toDelete);
                    try
                    {
                        m_DBApplContext.SaveChanges();
                    }
                    catch (Exception ex)
                    {
                        throw new DALException(CommonMessagesGenerator.GenerateDBException(ex.Message, "DeleteWorker"));
                    }
                }
                else
                {
                    throw new DALException(CommonMessagesGenerator.GenerateNotExistObjectMessage("Worker", workerID, null));
                }
            }
        }

        public void AddWorker(Worker workerToAdd, bool saveChanges = false)
        {
            lock (s_DbAccessLockObject)
            {
                Worker worker = getWorkerByID(workerToAdd.WorkerID);
                if (worker == null)
                {
                    m_DBApplContext.Workers.AddObject(workerToAdd);
                    if (saveChanges)
                    {
                        try
                        {
                            m_DBApplContext.SaveChanges();
                        }
                        catch (Exception ex)
                        {
                            throw new DALException(CommonMessagesGenerator.GenerateDBException(ex.Message, "AddWorker"));
                        }
                    }
                }
                else
                {
                    throw new DALException(CommonMessagesGenerator.GenerateAllreadyExistObjectMessage("Worker", worker.WorkerID, null));
                }
            }
        }

        public void UpdateWorkerName(string id, string newName, bool saveChanges = false)
        {
            lock (s_DbAccessLockObject)
            {
                Worker workerToUpdate = getWorkerByID(id);
                if (workerToUpdate != null)
                {
                    workerToUpdate.Name = newName;
                    if (saveChanges)
                    {
                        try
                        {
                            m_DBApplContext.SaveChanges();
                        }
                        catch (Exception ex)
                        {
                            throw new DALException(CommonMessagesGenerator.GenerateDBException(ex.Message, "UpdateWorkerName"));
                        }
                    }
                }
                else
                {
                    throw new DALException(CommonMessagesGenerator.GenerateNotExistObjectMessage("Worker", id, null));
                }
            }
        }

        public void UpdateWorkerLastName(string id, string newLastName, bool saveChanges = false)
        {
            lock (s_DbAccessLockObject)
            {
                Worker workerToUpdate = getWorkerByID(id);
                if (workerToUpdate != null)
                {
                    workerToUpdate.LastName = newLastName;
                    if (saveChanges)
                    {
                        try
                        {
                            m_DBApplContext.SaveChanges();
                        }
                        catch (Exception ex)
                        {
                            throw new DALException(CommonMessagesGenerator.GenerateDBException(ex.Message, "UpdateWorkerLastName"));
                        }
                    }
                }
                else
                {
                    throw new DALException(CommonMessagesGenerator.GenerateNotExistObjectMessage("Worker", id, null));
                }
            }
        }

        public void UpdateWorkerEmailAdress(string id, string emailAdress, bool saveChanges = false)
        {
            lock (s_DbAccessLockObject)
            {
                Worker workerToUpdate = getWorkerByID(id);
                if (workerToUpdate != null)
                {
                    workerToUpdate.EmailAdress = emailAdress;
                    if (saveChanges)
                    {
                        try
                        {
                            m_DBApplContext.SaveChanges();
                        }
                        catch (Exception ex)
                        {
                            throw new DALException(CommonMessagesGenerator.GenerateDBException(ex.Message, "UpdateWorkerEmailAdress"));
                        }
                    }
                }
                else
                {
                    throw new DALException(CommonMessagesGenerator.GenerateNotExistObjectMessage("Worker", id, null));
                }
            }
        }

        public void UpdateWorkerBirthDate(string id, DateTime newBirthDate, bool saveChanges = false)
        {
            lock (s_DbAccessLockObject)
            {
                Worker workerToUpdate = getWorkerByID(id);
                if (workerToUpdate != null)
                {
                    workerToUpdate.BirthDate = newBirthDate;
                    if (saveChanges)
                    {
                        try
                        {
                            m_DBApplContext.SaveChanges();
                        }
                        catch (Exception ex)
                        {
                            throw new DALException(CommonMessagesGenerator.GenerateDBException(ex.Message, "UpdateWorkerBirthDate"));
                        }
                    }
                }
                else
                {
                    throw new DALException(CommonMessagesGenerator.GenerateNotExistObjectMessage("Worker", id, null)); 
                }
            }
        }

        public void UpdateWorkerUserName(string id, string newUserName, bool saveChanges = false)
        {
            lock (s_DbAccessLockObject)
            {
                Worker workerToUpdate = getWorkerByID(id);
                if (workerToUpdate != null)
                {
                    workerToUpdate.UserName = newUserName;
                    if (saveChanges)
                    {
                        try
                        {
                            m_DBApplContext.SaveChanges();
                        }
                        catch (Exception ex)
                        {
                            throw new DALException(CommonMessagesGenerator.GenerateDBException(ex.Message, "UpdateWorkerUserName"));
                        }
                    }
                }
                else
                {
                    throw new DALException(CommonMessagesGenerator.GenerateNotExistObjectMessage("Worker", id, null)); 
                }
            }
        }

        public void UpdateWorkerPassword(string id, string newPassword, bool saveChanges = false)
        {
            lock (s_DbAccessLockObject)
            {
                Worker workerToUpdate = getWorkerByID(id);
                if (workerToUpdate != null)
                {
                    workerToUpdate.Password = newPassword;
                    if (saveChanges)
                    {
                        try
                        {
                            m_DBApplContext.SaveChanges();
                        }
                        catch (Exception ex)
                        {
                            throw new DALException(CommonMessagesGenerator.GenerateDBException(ex.Message, "UpdateWorkerPassword"));
                        }
                    }
                }
                else
                {
                    throw new DALException(CommonMessagesGenerator.GenerateNotExistObjectMessage("Worker", id, null));
                }
            }
        }

        public void UpdateWorkerStartOfWork(string id, DateTime newStartOfWork, bool saveChanges = false)
        {
            lock (s_DbAccessLockObject)
            {
                Worker workerToUpdate = getWorkerByID(id);
                if (workerToUpdate != null)
                {
                    workerToUpdate.StartOfWork = newStartOfWork;
                    if (saveChanges)
                    {
                        try
                        {
                            m_DBApplContext.SaveChanges();
                        }
                        catch (Exception ex)
                        {
                            throw new DALException(CommonMessagesGenerator.GenerateDBException(ex.Message, "UpdateWorkerStartOfWork"));
                        }
                    }
                }
                else
                {
                    throw new DALException(CommonMessagesGenerator.GenerateNotExistObjectMessage("Worker", id, null)); 
                }
            }
        }

        public void UpdateWorkerMaxNumberOfShifts(string id, int newMaxNumberOfShifts, bool saveChanges = false)
        {
            lock (s_DbAccessLockObject)
            {
                Worker workerToUpdate = getWorkerByID(id);
                if (workerToUpdate != null)
                {
                    workerToUpdate.MaxNumberOfShifts = newMaxNumberOfShifts;
                    if (saveChanges)
                    {
                        try
                        {
                            m_DBApplContext.SaveChanges();
                        }
                        catch (Exception ex)
                        {
                            throw new DALException(CommonMessagesGenerator.GenerateDBException(ex.Message, "UpdateWorkerMaxNumberOfShifts"));
                        }
                    }
                }
                else
                {
                    throw new DALException(CommonMessagesGenerator.GenerateNotExistObjectMessage("Worker", id, null));
                }
            }
        }
        #endregion
        # region Shift Table CRUD
        public IEnumerable<Shift> GetAllShifts()
        {
            return m_DBApplContext.Shifts;   
        }

        public Shift GetShiftByID(string id)
        {
            Shift shiftToReturn = null;
            lock (s_DbAccessLockObject)
            {
                shiftToReturn = getShiftByID(id);

                if (shiftToReturn == null)
                {
                    throw new DALException(CommonMessagesGenerator.GenerateNotExistObjectMessage("Shift", id, null));
                }
                shiftToReturn = m_DBApplContext.Shifts.SingleOrDefault(p => p.ShiftID == id);
            }
            return shiftToReturn;
        }

        private Shift getShiftByID(string id)
        {
            Shift shiftToReturn = null;
            lock (s_DbAccessLockObject)
            {
                shiftToReturn = m_DBApplContext.Shifts.SingleOrDefault(p => p.ShiftID == id);
            }
            return shiftToReturn;
        }

        public List<Shift> GetShiftsByRange(DateTime start, DateTime end)
        {
            List<Shift> shiftsToReturn = new List<Shift>();
            lock (s_DbAccessLockObject)
            {
                foreach (Shift shift in m_DBApplContext.Shifts)
                {
                    if (shift.BeginTime >= start && shift.EndTime <= end)
                    {
                        shiftsToReturn.Add(shift);
                    }
                }
            }
            return shiftsToReturn;
        }
        public Shift GetShiftByDateRange(DateTime startTime, DateTime endTime)
        {
            Shift shiftToReturn = null;
            lock (s_DbAccessLockObject)
            {
                foreach (var p in m_DBApplContext.Shifts)
                {
                    if (DateTime.Compare(p.BeginTime.Date, startTime.Date) == 0
                        && p.BeginTime.Hour == startTime.Hour
                        && p.BeginTime.Minute == startTime.Minute
                        && DateTime.Compare(p.EndTime.Date, endTime.Date) == 0
                        && p.EndTime.Hour == endTime.Hour
                        && p.EndTime.Minute == endTime.Minute)
                    {
                        shiftToReturn = p;
                        break;
                    }

                }
                if (shiftToReturn == null)
                {
                    throw new DALException(CommonMessagesGenerator.GenerateNotExistObjectMessage("Shift",
                                                                                                 startTime.
                                                                                                     ToShortTimeString() +
                                                                                                 startTime.
                                                                                                     ToShortTimeString(),
                                                                                                 null));
                }
            }
            return shiftToReturn;
        }

        public void DeleteShift(string shiftID, bool saveChanges = false)
        {
            lock (s_DbAccessLockObject)
            {
                Shift toDelete = getShiftByID(shiftID);
                if (toDelete != null)
                {
                    m_DBApplContext.Shifts.DeleteObject(toDelete);
                    if (saveChanges)
                    {
                        try
                        {
                            m_DBApplContext.SaveChanges();
                        }
                        catch (Exception ex)
                        {
                            throw new DALException(CommonMessagesGenerator.GenerateDBException(ex.Message, "DeleteShift"));
                        }
                    }
                }
                else
                {
                    throw new DALException(CommonMessagesGenerator.GenerateNotExistObjectMessage("Shift", shiftID, null));
                }
            }
        }

        public void AddShift(Shift shiftToAdd, bool saveChanges = false)
        {
            lock (s_DbAccessLockObject)
            {
                Shift shift = getShiftByID(shiftToAdd.ShiftID);
                if (shift == null)
                {
                    m_DBApplContext.Shifts.AddObject(shiftToAdd);
                    if (saveChanges)
                    {
                        try
                        {
                            m_DBApplContext.SaveChanges();
                        }
                        catch (Exception ex)
                        {
                            throw new DALException(CommonMessagesGenerator.GenerateDBException(ex.Message, "AddShift"));
                        }
                    }
                }
                else
                {
                    throw new DALException(CommonMessagesGenerator.GenerateAllreadyExistObjectMessage("Shift", shiftToAdd.ShiftID, null));
                }
            }
        }

        public void UpdateShiftName(string id, string newName, bool saveChanges = false)
        {
            lock (s_DbAccessLockObject)
            {
                Shift shiftToUpdate = getShiftByID(id);
                if (shiftToUpdate != null)
                {
                    shiftToUpdate.Name = newName;
                    if (saveChanges)
                    {
                        try
                        {
                            m_DBApplContext.SaveChanges();
                        }
                        catch (Exception ex)
                        {
                            throw new DALException(CommonMessagesGenerator.GenerateDBException(ex.Message, "UpdateShiftName"));
                        }
                    }
                }
                else
                {
                    throw new DALException(CommonMessagesGenerator.GenerateNotExistObjectMessage("Shift", id, null)); 
                }
            }
        }

        public void UpdateNumberOfWorkersNeeded(string shiftId, int newNumberNeeded, bool saveChanges = false)
        {
            lock (s_DbAccessLockObject)
            {
                Shift shiftToUpdate = getShiftByID(shiftId);
                if (shiftToUpdate != null)
                {
                    shiftToUpdate.NumberOfWorkersNeeded = newNumberNeeded;
                    if (saveChanges)
                    {
                        try
                        {
                            m_DBApplContext.SaveChanges();
                        }
                        catch (Exception ex)
                        {
                            throw new DALException(CommonMessagesGenerator.GenerateDBException(ex.Message, "UpdateNumberOfWorkersNeeded"));
                        }
                    }
                }
                else
                {
                    throw new DALException(CommonMessagesGenerator.GenerateNotExistObjectMessage("Shift", shiftId, null));
                }
            }
        }

        public void UpdateStartTime(string shiftId, DateTime newStartTime, bool saveChanges = false)
        {
            lock (s_DbAccessLockObject)
            {
                Shift shiftToUpdate = getShiftByID(shiftId);
                if (shiftToUpdate != null)
                {
                    shiftToUpdate.BeginTime = newStartTime;
                    if (saveChanges)
                    {
                        try
                        {
                            m_DBApplContext.SaveChanges();
                        }
                        catch (Exception ex)
                        {
                            throw new DALException(CommonMessagesGenerator.GenerateDBException(ex.Message, "UpdateStartTime"));
                        }
                    }
                }
                else
                {
                    throw new DALException(CommonMessagesGenerator.GenerateNotExistObjectMessage("Shift", shiftId, null));
                }
            }
        }

        public void UpdateEndTime(string shiftId, DateTime newEndTime, bool saveChanges = false)
        {
            lock (s_DbAccessLockObject)
            {
                Shift shiftToUpdate = getShiftByID(shiftId);
                if (shiftToUpdate != null)
                {
                    shiftToUpdate.EndTime = newEndTime;
                    if (saveChanges)
                    {
                        try
                        {
                            m_DBApplContext.SaveChanges();
                        }
                        catch (Exception ex)
                        {
                            throw new DALException(CommonMessagesGenerator.GenerateDBException(ex.Message, "UpdateEndTime"));
                        }
                    }
                }
                else
                {
                    throw new DALException(CommonMessagesGenerator.GenerateNotExistObjectMessage("Shift", shiftId, null));
                }
            }
        }
        #endregion

        #region WorkerStatInAWeek
        public WorkerStatInAWeek GetWorkerStat(DateTime startTime, DateTime endTime, string workerId)
        {
            WorkerStatInAWeek workerStatInAWeek = null;
            lock (s_DbAccessLockObject)
            {
                workerStatInAWeek = getWorkerStat(startTime, endTime, workerId);
                if (workerStatInAWeek == null)
                {
                    throw new DALException(CommonMessagesGenerator.GenerateNotExistObjectMessage("Worker", workerId,
                                                                                                 "TimeRange", startTime.ToShortDateString() + "-" + endTime.ToShortDateString(), null));
                }
            }
            return workerStatInAWeek;
        }

        private WorkerStatInAWeek getWorkerStat(DateTime startTime, DateTime endTime, string workerId)
        {
            WorkerStatInAWeek workerStatInAWeek = null;
            lock (s_DbAccessLockObject)
            {
                workerStatInAWeek =
                    m_DBApplContext.WorkerStatInAWeeks.SingleOrDefault(p => p.WorkerID == workerId
                        && p.WeekStartTime == startTime && p.WeekEndTime == endTime);
            }
            return workerStatInAWeek;
        }

        public bool IsWorkerStatExist(DateTime startTime, DateTime endTime, string workerId)
        {
            return getWorkerStat(startTime, endTime, workerId) != null;
        }

        public WorkerStatInAWeek GetMinimalWorkerStat(string workerId)
        {
            WorkerStatInAWeek minWorkerStat = null;
            DateTime minDate = new DateTime(2020, 1, 1);
            lock (s_DbAccessLockObject)
            {
                foreach (var workerStat in m_DBApplContext.WorkerStatInAWeeks)
                {
                    if(workerStat.WeekStartTime <= minDate)
                    {
                        minDate = workerStat.WeekStartTime;
                        minWorkerStat = workerStat;
                    }
                }
            }
            return minWorkerStat;
        }

        public void DeleteWorkerStat(DateTime startTime, DateTime endTime, string workerId, bool saveChanges = false)
        {
            lock (s_DbAccessLockObject)
            {
                WorkerStatInAWeek toDelete = getWorkerStat(startTime, endTime, workerId);
                if (toDelete != null)
                {
                    m_DBApplContext.WorkerStatInAWeeks.DeleteObject(toDelete);
                    if (saveChanges)
                    {
                        try
                        {
                            m_DBApplContext.SaveChanges();
                        }
                        catch (Exception ex)
                        {
                            throw new DALException(CommonMessagesGenerator.GenerateDBException(ex.Message, "DeleteWorkerStat"));
                        }
                    }
                }
                else
                {
                    throw new DALException(CommonMessagesGenerator.GenerateNotExistObjectMessage("Worker In Time Range", workerId + " " +
                        startTime.ToShortDateString() + "-" + endTime.ToShortDateString(), null));
                }
            }
        }

        public void AddWorkerStat(WorkerStatInAWeek workerStat, bool saveChanges = false)
        {
            lock (s_DbAccessLockObject)
            {
                if (getWorkerStat(workerStat.WeekStartTime, workerStat.WeekEndTime, workerStat.WorkerID) == null)
                {
                    m_DBApplContext.WorkerStatInAWeeks.AddObject(workerStat);
                    if (saveChanges)
                    {
                        try
                        {
                            m_DBApplContext.SaveChanges();
                        }
                        catch (Exception ex)
                        {
                            throw new DALException(CommonMessagesGenerator.GenerateDBException(ex.Message,
                                                                                                "AddWorkerStat"));
                        }
                    }
  
                }
                else
                {
                    throw new DALException(CommonMessagesGenerator.GenerateAllreadyExistObjectMessage("Worker In Time Range", workerStat.WorkerID + " " +
                        workerStat.WeekStartTime.ToShortDateString() + "-" + workerStat.WeekEndTime.ToShortDateString(), null));
                }
            }
        }

        private void deleteAllWorkerStatInDateRange(string workerId, DateTime startTime, DateTime endTime, bool saveChanges = false)
        {
            Worker worker = getWorkerByID(workerId);
            if (worker != null)
            {
                List<WorkerStatInAWeek> copyOfWorkerStatInAWeek = worker.WorkerStatInAWeeks.ToList();
                foreach (var workerStatInAWeek in copyOfWorkerStatInAWeek)
                {
                    if (workerStatInAWeek.WeekStartTime == startTime && workerStatInAWeek.WeekEndTime == endTime)
                    {
                        worker.WorkerStatInAWeeks.Remove(workerStatInAWeek);
                    }
                }
                if (saveChanges)
                {
                    try
                    {
                        m_DBApplContext.SaveChanges();
                    }
                    catch (Exception ex)
                    {

                        throw new DALException(CommonMessagesGenerator.GenerateDBException(ex.Message,
                                                                                            "deleteAllWorkerStatInDateRange"));
                    }
                }
            }
            else
            {
                throw new DALException(CommonMessagesGenerator.GenerateNotExistObjectMessage("Worker", workerId,
                                                                                                null));
            }
        }

        public void DeleteStatInDateRangeForAllWorkers(DateTime startTime, DateTime endTime, bool saveChanges = false)
        {
            lock (s_DbAccessLockObject)
            {
                List<Worker> allWorkers = GetAllWorkers().ToList();
                foreach (var worker in allWorkers)
                {
                    deleteAllWorkerStatInDateRange(worker.WorkerID, startTime, endTime, saveChanges);
                }
            }
        }

        #endregion
        # region Request Table CRUD
        public IEnumerable<Request> GetAllRequests()
        {
            return m_DBApplContext.Requests;
        }

        public Request GetRequestByID(string workerId, string shiftId)
        {
            Request requestToReturn = null;
            lock (s_DbAccessLockObject)
            {
                requestToReturn = getRequestByID(workerId, shiftId);

                if (requestToReturn == null)
                {
                    throw new DALException(CommonMessagesGenerator.GenerateNotExistObjectMessage("Worker", workerId,
                                                                                                 "Shift", shiftId, null));
                }
                requestToReturn =
                    m_DBApplContext.Requests.SingleOrDefault(p => p.ShiftID == shiftId && p.WorkerID == workerId);
            }
            return requestToReturn;
        }

        private Request getRequestByID(string workerId, string shiftId)
        {
            Request requestToReturn = null;
            lock (s_DbAccessLockObject)
            {    
                requestToReturn =
                    m_DBApplContext.Requests.SingleOrDefault(p => p.ShiftID == shiftId && p.WorkerID == workerId);            
            }
            return requestToReturn;
        }

        public void DeleteRequest(string workerId, string shiftId, bool saveChanges = false)
        {
            lock (s_DbAccessLockObject)
            {
                Request toDelete = getRequestByID(workerId, shiftId);
                if (toDelete != null)
                {
                    m_DBApplContext.Requests.DeleteObject(toDelete);
                    if (saveChanges)
                    {
                        try
                        {
                            m_DBApplContext.SaveChanges();
                        }
                        catch (Exception ex)
                        {
                            throw new DALException(CommonMessagesGenerator.GenerateDBException(ex.Message, "DeleteRequest"));
                        }
                    }
                }
                else
                {
                    throw new DALException(CommonMessagesGenerator.GenerateNotExistObjectMessage("Worker", workerId, "Shift", shiftId, null));
                }
            }
        }

        public void UpdateRequestType(string workerId, string shiftId, string newRequestType, bool saveChanges = false)
        {
            lock (s_DbAccessLockObject)
            {
                Request requestToUpdate = getRequestByID(workerId, shiftId);
                if (requestToUpdate != null)
                {
                    requestToUpdate.Type = newRequestType;
                    if (saveChanges)
                    {
                        try
                        {
                            m_DBApplContext.SaveChanges();
                        }
                        catch (Exception ex)
                        {
                            throw new DALException(CommonMessagesGenerator.GenerateDBException(ex.Message, "UpdateRequestType"));
                        }
                    }
                }
                else
                {
                    throw new DALException(CommonMessagesGenerator.GenerateNotExistObjectMessage("Worker", workerId, "Shift", shiftId, "as request"));
                }
            }
        }

        private void addRequestShiftToWorker(string shiftId, string workerId, string type, bool saveChanges = false)
        {
            Worker worker = null;
            Shift shift = null;
            lock (s_DbAccessLockObject)
            {
                worker = getWorkerByID(workerId);
                shift = getShiftByID(shiftId);
                if (worker != null && shift != null)
                {
                    if (getRequestByID(workerId, shiftId) == null)
                    {
                        m_DBApplContext.Requests.AddObject(new Request {Worker = worker, Shift = shift, Type = type});
                        if (saveChanges)
                        {
                            try
                            {
                                m_DBApplContext.SaveChanges();
                            }
                            catch (Exception ex)
                            {
                                throw new DALException(CommonMessagesGenerator.GenerateDBException(ex.Message,
                                                                                                    "AddRequestShiftToWorker"));
                            }
                        }
                    }
                    else
                    {
                        throw new DALException(CommonMessagesGenerator.GenerateAllreadyExistObjectMessage("Request", workerId + "-->" + shiftId, null));
                    }
                }
                else
                {
                    throw new DALException(CommonMessagesGenerator.GenerateNotExistObjectMessage("Worker", workerId, "Shift", shiftId, null));
                }
            }
        }

        public void RenterAllRequestsOfWorker(string workerId, DateTime startTime, DateTime endTime, List<Request> newRequests, bool saveChanges = false)
        {
            lock (s_DbAccessLockObject)
            {
                deleteAllRequestsForWorkerInDateRange(workerId, startTime, endTime, true);
                foreach (var newRequest in newRequests)
                {
                    addRequestShiftToWorker(newRequest.ShiftID, workerId, newRequest.Type, true);
                }
                m_DBApplContext.SaveChanges();
            }
        }

        private void deleteAllRequestsForWorkerInDateRange(string workerId, DateTime startTime, DateTime endTime, bool saveChanges = false)
        {
            Worker worker = getWorkerByID(workerId);
            if (worker != null)
            {
                List<Request> copyOfRequests = worker.Requests.ToList();
                foreach (var request in copyOfRequests)
                {
                    if (request.Shift.BeginTime >= startTime && request.Shift.EndTime <= endTime)
                    {
                        worker.Requests.Remove(request);
                    }
                }
                if (saveChanges)
                {
                    try
                    {
                        m_DBApplContext.SaveChanges();
                    }
                    catch (Exception ex)
                    {

                        throw new DALException(CommonMessagesGenerator.GenerateDBException(ex.Message,
                                                                                            "DeleteAllRequestsForWorker"));
                    }
                }
            }
            else
            {
                throw new DALException(CommonMessagesGenerator.GenerateNotExistObjectMessage("Worker", workerId,
                                                                                                null));
            }
        }

        public List<Worker> GetAllWorkersThatAskedForShiftsInRange(DateTime startTime, DateTime endTime)
        {
            List<Worker> workersWithRequests = new List<Worker>();
            lock (s_DbAccessLockObject)
            {
                List<Shift> shifts = GetShiftsByRange(startTime, endTime);
                List<Worker> workers = GetAllWorkers().ToList();
                workersWithRequests.AddRange(workers.Where(worker => shifts.Any(shift => getRequestByID(worker.WorkerID, shift.ShiftID) != null)));
            }
            return workersWithRequests;
        }

        public List<Worker> GetAllWorkersThatNotAskedForanyShiftInRange(DateTime startTime, DateTime endTime)
        {
            List<Worker> workersWithNoRequests = new List<Worker>();
            lock (s_DbAccessLockObject)
            {
                List<Shift> shifts = GetShiftsByRange(startTime, endTime);
                List<Worker> workers = GetAllWorkers().ToList();
                workersWithNoRequests.AddRange(from worker in workers let isHaveAtLeastOneShift = shifts.Any(shift => getRequestByID(worker.WorkerID, shift.ShiftID) != null) where !isHaveAtLeastOneShift select worker);
            }
            return workersWithNoRequests;
        }

        #endregion
        # region Result Table CRUD
        public IEnumerable<Result> GetAllResults()
        {
            return m_DBApplContext.Results;
        }

        public Result GetResultByID(string workerId, string shiftId)
        {
            Result requestToReturn = getResultByID(workerId, shiftId);
            if(requestToReturn == null)
            {
                throw new DALException(CommonMessagesGenerator.GenerateNotExistObjectMessage("Worker", workerId, "Shift", shiftId, null));                                
            }
            requestToReturn = m_DBApplContext.Results.SingleOrDefault(p => p.ShiftID == shiftId && p.WorkerID == workerId);
            return requestToReturn;
        }

        private Result getResultByID(string workerId, string shiftId)
        {
            Result requestToReturn = null;
            requestToReturn = m_DBApplContext.Results.SingleOrDefault(p => p.ShiftID == shiftId && p.WorkerID == workerId);
            return requestToReturn;
        }


        public void AddResultShiftToWorker(string workerId, DateTime startTime, DateTime endTime, string resultType,  bool saveChanges = false)
        {
            Worker workerToRegister = null;
            Shift shiftToRegister = null;
            lock (s_DbAccessLockObject)
            {
                workerToRegister = getWorkerByID(workerId);
                shiftToRegister = GetShiftByDateRange(startTime, endTime);
                if (workerToRegister != null && shiftToRegister != null)
                {
                    if (getResultByID(workerId, shiftToRegister.ShiftID) == null)
                    {
                        m_DBApplContext.Results.AddObject(new Result { Worker = workerToRegister, Shift = shiftToRegister, Step = resultType });
                        if (saveChanges)
                        {
                            try
                            {
                                m_DBApplContext.SaveChanges();
                            }
                            catch (Exception ex)
                            {
                                throw new DALException(CommonMessagesGenerator.GenerateDBException(ex.Message,
                                                                                                   "AddResultShiftToWorker"));
                            }
                        }
                    }
                    else
                    {
                        throw new DALException(CommonMessagesGenerator.GenerateAllreadyExistObjectMessage("Result", workerId + "-->" + shiftToRegister.ShiftID, null));
                    }
                }
                else
                {
                    throw new DALException(CommonMessagesGenerator.GenerateNotExistObjectMessage("Worker", workerId, "Shift", shiftToRegister.ShiftID, null));
                }
            }
        }

        public void DeleteResult(string workerId, DateTime startTime, DateTime endTime, bool saveChanges = false)
        {
            Result toDelete = null;
            lock (s_DbAccessLockObject)
            {
                Shift shift = GetShiftByDateRange(startTime, endTime);
                toDelete = getResultByID(workerId, shift.ShiftID);
                if (toDelete != null)
                {
                    m_DBApplContext.Results.DeleteObject(toDelete);
                    if (saveChanges)
                    {
                        try
                        {
                            m_DBApplContext.SaveChanges();
                        }
                        catch (Exception ex)
                        {
                            throw new DALException(CommonMessagesGenerator.GenerateDBException(ex.Message, "DeleteResult"));
                        }
                    }
                }
                else
                {
                    throw new DALException(CommonMessagesGenerator.GenerateNotExistObjectMessage("Worker", workerId, "Shift", shift.ShiftID, null));
                }
            }
        }

        public void DeleteResult(string workerId, string shiftId, bool saveChanges = false)
        {
            Result toDelete = getResultByID(workerId, shiftId);
            lock (s_DbAccessLockObject)
            {
                if (toDelete != null)
                {
                    m_DBApplContext.Results.DeleteObject(toDelete);
                    if (saveChanges)
                    {
                        try
                        {
                            m_DBApplContext.SaveChanges();
                        }
                        catch (Exception ex)
                        {
                            throw new DALException(CommonMessagesGenerator.GenerateDBException(ex.Message, "DeleteResult"));
                        }
                    }
                }
                else
                {
                    throw new DALException(CommonMessagesGenerator.GenerateNotExistObjectMessage("Worker", workerId, "Shift", shiftId, null));
                }
            }
        }

        public void UpdateResultType(string workerId, string shiftId, string newResultType, bool saveChanges = false)
        {
            Result requestToUpdate = getResultByID(workerId, shiftId);
            lock (s_DbAccessLockObject)
            {
                if (requestToUpdate != null)
                {
                    requestToUpdate.Step = newResultType;
                    if (saveChanges)
                    {
                        try
                        {
                            m_DBApplContext.SaveChanges();
                        }
                        catch (Exception ex)
                        {
                            throw new DALException(CommonMessagesGenerator.GenerateDBException(ex.Message, "UpdateResultType"));
                        }
                    }
                }
                else
                {
                    throw new DALException(CommonMessagesGenerator.GenerateNotExistObjectMessage("Worker", workerId, "Shift", shiftId, "as result"));
                }
            }
        }

        public void AddResultShiftToWorker(string shiftId, string workerId, string step, bool saveChanges = false)
        {
            Worker worker = getWorkerByID(workerId);
            Shift shift = getShiftByID(shiftId);
            lock (s_DbAccessLockObject)
            {
                if (worker != null && shift != null)
                {
                    if (getResultByID(workerId, shiftId) == null)
                    {
                        m_DBApplContext.Results.AddObject(new Result {Worker = worker, Shift = shift, Step = step});
                        if (saveChanges)
                        {
                            try
                            {
                                m_DBApplContext.SaveChanges();
                            }
                            catch (Exception ex)
                            {
                                throw new DALException(CommonMessagesGenerator.GenerateDBException(ex.Message,
                                                                                                   "AddResultShiftToWorker"));
                            }
                        }
                    }
                    else
                    {
                        throw new DALException(CommonMessagesGenerator.GenerateAllreadyExistObjectMessage("Result", workerId + "-->" + shiftId,  null));
                    }
                }
                else
                {
                    throw new DALException(CommonMessagesGenerator.GenerateNotExistObjectMessage("Worker", workerId, "Shift", shiftId, null));
                }
            }
        }

        public bool IsWorkerGotShift(string workerId, DateTime startTime, DateTime endTime)
        {
            bool isEmpty = true;
            IQueryable<Result> query;
            lock (s_DbAccessLockObject)
            {
                query = from result in m_DBApplContext.Results
                                           where result.Shift.BeginTime == startTime && result.Shift.EndTime == endTime
                                                      && result.WorkerID == workerId
                                            select result;

            }

            return query.ToList().Count > 0;
        }

        public void DeleteAllResultsInTimeRange(DateTime startTime, DateTime endTime, bool saveChanges = false)
        {
            lock (s_DbAccessLockObject)
            {
                List<Result> copyOfresults = m_DBApplContext.Results.ToList();
                foreach (var result in copyOfresults)
                {
                    if (result.Shift.BeginTime >= startTime && result.Shift.BeginTime <= endTime)
                    {
                         m_DBApplContext.Results.DeleteObject(result);
                    }
                }

                if (saveChanges)
                {
                    try
                    {
                        m_DBApplContext.SaveChanges();
                    }
                    catch (Exception ex)
                    {

                        throw new DALException(CommonMessagesGenerator.GenerateDBException(ex.Message,
                                                                                            "DeleteAllResultsInTimeRange"));
                    }
                }
            }
        }

        #endregion
        # region General Queries
        public List<Worker> GetAllWorkersThatAskedForShift(string shiftID, string type, DateTime beginTime, DateTime endTime)
        {
            List<Worker> workers = new List<Worker>();
            lock (s_DbAccessLockObject)
            {   
                IQueryable<Request> query = from request in m_DBApplContext.Requests
                                            where request.ShiftID == shiftID && request.Type == type
                                            select request;
                foreach (Request result in query)
                {
                    if (result.Shift.BeginTime >= beginTime && result.Shift.EndTime <= endTime)
                    {
                        workers.Add(result.Worker);
                    }
                }
            }
            return workers;
        }

        public List<Worker> GetAllWorkersThatAskedForShift(string shiftID, DateTime beginTime, DateTime endTime)
        {
            List<Worker> workers = new List<Worker>();
            lock (s_DbAccessLockObject)
            {
                IQueryable<Request> query = from request in m_DBApplContext.Requests
                                            where request.ShiftID == shiftID
                                            select request;
                foreach (Request result in query)
                {
                    if (result.Shift.BeginTime >= beginTime && result.Shift.EndTime <= endTime)
                    {
                        workers.Add(result.Worker);
                    }
                }
            }
            return workers;
        }

        public List<Shift> GetAllShiftsThatWorkerAsked(string workerId, string type, DateTime beginTime, DateTime endTime)
        {
            List<Shift> shifts = new List<Shift>();
            lock (s_DbAccessLockObject)
            {
                IQueryable<Request> query = from request in m_DBApplContext.Requests
                                            where request.WorkerID == workerId && request.Type == type
                                            select request;
                foreach (Request result in query)
                {
                    if (result.Shift.BeginTime >= beginTime && result.Shift.EndTime <= endTime)
                    {
                        shifts.Add(result.Shift);
                    }
                }
            }
            return shifts;
        }

        public List<string> GetAllShiftsIdsThatWorkerAsked(string workerId, string type, DateTime beginTime, DateTime endTime)
        {
            List<string> shifts = new List<string>();
            lock (s_DbAccessLockObject)
            {
                IQueryable<Request> query = from request in m_DBApplContext.Requests
                                            where request.WorkerID == workerId && request.Type == type
                                            select request;
                foreach (Request result in query)
                {
                    if (result.Shift.BeginTime >= beginTime && result.Shift.EndTime <= endTime)
                    {
                        shifts.Add(result.ShiftID);
                    }
                }
            }
            return shifts;
        }

        public List<Shift> GetAllShiftsThatWorkerAsked(string workerId, DateTime beginTime, DateTime endTime)
        {
            List<Shift> shifts = new List<Shift>();
            lock (s_DbAccessLockObject)
            {
                IQueryable<Request> query = from request in m_DBApplContext.Requests
                                            where request.WorkerID == workerId
                                            select request;

                foreach (Request result in query)
                {
                    if (result.Shift.BeginTime >= beginTime && result.Shift.EndTime <= endTime)
                    {
                        shifts.Add(result.Shift);
                    }
                }
            }
            return shifts;
        }

        public List<Shift> GetAllShiftsThatWorkerGot(string workerId, DateTime beginTime, DateTime endTime)
        {
            List<Shift> shifts = new List<Shift>();
            lock (s_DbAccessLockObject)
            {
                IQueryable<Result> query = from result in m_DBApplContext.Results
                                           where result.WorkerID == workerId
                                           select result;
                foreach (Result result in query)
                {
                    if (result.Shift.BeginTime >= beginTime && result.Shift.EndTime <= endTime)
                    {
                        shifts.Add(result.Shift);
                    }
                }
            }
            return shifts;
        }

        public List<Shift> GetAllShiftsThatWorkerGot(string workerId, string step, DateTime beginTime, DateTime endTime)
        {
            List<Shift> shifts = new List<Shift>();
            lock (s_DbAccessLockObject)
            {
                IQueryable<Result> query = from result in m_DBApplContext.Results
                                           where result.WorkerID == workerId && result.Step == step
                                           select result;
                foreach (Result result in query)
                {
                    if (result.Shift.BeginTime >= beginTime && result.Shift.EndTime <= endTime)
                    {
                        shifts.Add(result.Shift);
                    }
                }
            }
            return shifts;
        }

        public List<Worker> GetAllWorkersThatAreRegisteredToShift(string shiftId, DateTime beginTime, DateTime endTime)
        {
            List<Worker> workers = new List<Worker>();
            lock (s_DbAccessLockObject)
            {
                IQueryable<Result> query = from result in m_DBApplContext.Results
                                           where result.ShiftID == shiftId
                                           select result;
                foreach (Result result in query)
                {
                    if (result.Shift.BeginTime >= beginTime && result.Shift.EndTime <= endTime)
                    {
                        workers.Add(result.Worker);
                    }
                }
            }
            return workers;
        }

        public List<Worker> GetAllWorkersThatAreRegisteredToShift(string shiftId, string step, DateTime beginTime, DateTime endTime)
        {
            List<Worker> workers = new List<Worker>();
            lock (s_DbAccessLockObject)
            {
                IQueryable<Result> query = from result in m_DBApplContext.Results
                                           where result.ShiftID == shiftId && result.Step == step
                                           select result;
                foreach (Result result in query)
                {
                    if (result.Shift.BeginTime >= beginTime && result.Shift.EndTime <= endTime)
                    {
                        workers.Add(result.Worker);
                    }
                }
            }
            return workers;
        }

        #endregion

        public void SaveChanges()
        {
            m_DBApplContext.SaveChanges();
        }

        public void DeleteDBContents()
        {
            m_DBApplContext.Requests.ToList().ForEach(r => m_DBApplContext.Requests.DeleteObject(r));
            m_DBApplContext.Results.ToList().ForEach(r => m_DBApplContext.Results.DeleteObject(r));
            m_DBApplContext.Shifts.ToList().ForEach(s => m_DBApplContext.Shifts.DeleteObject(s));
            m_DBApplContext.Managers.ToList().ForEach(m => m_DBApplContext.Managers.DeleteObject(m));
            m_DBApplContext.Workers.ToList().ForEach(w => m_DBApplContext.Workers.DeleteObject(w));

            m_DBApplContext.SaveChanges();
        }
    }
}
